// app/service/import_data_service.js
'use strict';

const { Service } = require('egg');
const fs = require('fs').promises;
const path = require('path');
const xlsx = require('xlsx');

class ImportService extends Service {
  // 处理银行流水导入 - 符合AC-API-004验收标准
  async processBankTransactions(filePath, importType) {
    const { ctx } = this;
    
    try {
      // 记录开始时间用于性能统计
      const startTime = Date.now();
      
      // 读取Excel文件
      const workbook = xlsx.readFile(filePath);
      const sheetName = workbook.SheetNames[0];
      const worksheet = workbook.Sheets[sheetName];
      
      // 转换为JSON数据
      const rawData = xlsx.utils.sheet_to_json(worksheet);
      
      // 性能检查 - 1000条数据应在5秒内完成
      if (rawData.length > 1000) {
        ctx.logger.info(`处理大量数据: ${rawData.length} 条记录`);
      }
      
      // 根据导入类型处理数据
      let processedData;
      switch (importType) {
      case 'zcy':
        processedData = this.processZcyData(rawData);
        break;
      case 'standard':
        processedData = this.processStandardData(rawData);
        break;
      default:
        throw new Error('不支持的导入类型');
      }
      
      // 自动去重 - 符合AC-API-004验收标准
      const deduplicatedData = await this.deduplicateTransactions(processedData);
      
      // 批量保存到数据库
      const importBatch = `batch_${Date.now()}`;
      const savedTransactions = [];
      let duplicateCount = 0;
      
      // 使用事务批量插入以提高性能
      await ctx.model.transaction(async (transaction) => {
        for (const item of deduplicatedData.uniqueData) {
          try {
            const existing = await ctx.model.BankTransactionModel.findOne({
              where: {
                transactionId: item.transactionId,
                amount: item.amount,
                transactionDate: item.transactionDate
              },
              transaction
            });
            
            if (existing) {
              duplicateCount++;
              continue;
            }
            
            const savedItem = await ctx.model.BankTransactionModel.create({
              ...item,
              importBatch: importBatch,
              dataSource: 'file_import'
            }, { transaction });
            
            savedTransactions.push(savedItem);
          } catch (saveError) {
            ctx.logger.error('保存单条记录失败:', saveError);
          }
        }
      });
      
      const endTime = Date.now();
      const processingTime = endTime - startTime;
      
      // 性能检查
      if (processingTime > 5000) {
        ctx.logger.warn(`导入处理时间较长: ${processingTime}ms for ${rawData.length} records`);
      }
      
      return {
        successCount: savedTransactions.length,
        duplicateCount: duplicateCount,
        totalCount: rawData.length,
        deduplicatedCount: deduplicatedData.uniqueData.length,
        processingTime: `${processingTime}ms`,
        importBatch: importBatch
      };
    } catch (error) {
      ctx.logger.error('处理银行流水导入失败:', error);
      throw error;
    }
  }
  
  // 自动去重功能 - 符合AC-API-004验收标准
  async deduplicateTransactions(data) {
    const uniqueData = [];
    const seen = new Set();
    const duplicates = [];
    
    for (const item of data) {
      // 创建唯一标识符
      const key = `${item.transactionId}_${item.amount}_${item.transactionDate}`;
      
      if (seen.has(key)) {
        duplicates.push(item);
      } else {
        seen.add(key);
        uniqueData.push(item);
      }
    }
    
    return {
      uniqueData,
      duplicates
    };
  }
  
  // 处理政采云数据
  processZcyData(rawData) {
    return rawData.map(item => ({
      transactionId: item['交易流水号'] || item['流水号'] || this.generateTransactionId(),
      payerName: item['付款方名称'] || item['付款人'] || '',
      payerAccount: item['付款方账号'] || item['付款账号'] || '',
      amount: this.parseAmount(item['交易金额'] || item['金额']),
      transactionDate: this.parseDate(item['交易日期'] || item['日期']),
      transactionTime: this.parseDateTime(item['交易时间'] || item['时间'] || item['交易日期'] || item['日期']),
      balance: this.parseAmount(item['账户余额'] || item['余额']) || 0,
      remark: item['交易摘要'] || item['摘要'] || item['备注'] || '',
      isFiltered: false
    }));
  }
  
  // 处理标准数据
  processStandardData(rawData) {
    return rawData.map(item => ({
      transactionId: item['transactionId'] || item['流水号'] || this.generateTransactionId(),
      payerName: item['payerName'] || item['付款方'] || '',
      payerAccount: item['payerAccount'] || item['账号'] || '',
      amount: this.parseAmount(item['amount'] || item['金额']),
      transactionDate: this.parseDate(item['transactionDate'] || item['交易日期']),
      transactionTime: this.parseDateTime(item['transactionTime'] || item['交易时间'] || item['transactionDate'] || item['交易日期']),
      balance: this.parseAmount(item['balance'] || item['余额']) || 0,
      remark: item['remark'] || item['备注'] || '',
      isFiltered: false
    }));
  }
  
  // 智能解析金额 - 支持多种格式
  parseAmount(amountStr) {
    if (typeof amountStr === 'number') return amountStr;
    if (!amountStr) return 0;
    
    // 移除货币符号和空格
    const cleanAmount = amountStr.toString().replace(/[¥$,€£\s]/g, '');
    return parseFloat(cleanAmount) || 0;
  }
  
  // 智能解析日期 - 符合AC-API-004验收标准
  parseDate(dateStr) {
    if (!dateStr) return new Date();
    
    // 处理Excel日期序列号
    if (typeof dateStr === 'number') {
      return new Date((dateStr - 25569) * 86400 * 1000);
    }
    
    // 转换为字符串处理
    const dateString = dateStr.toString().trim();
    
    // 尝试解析常见日期格式
    const formats = [
      // YYYY-MM-DD
      /^\d{4}-\d{1,2}-\d{1,2}$/,
      // YYYY/MM/DD
      /^\d{4}\/\d{1,2}\/\d{1,2}$/,
      // MM/DD/YYYY
      /^\d{1,2}\/\d{1,2}\/\d{4}$/,
      // DD/MM/YYYY
      /^\d{1,2}\/\d{1,2}\/\d{4}$/,
      // YYYY年MM月DD日
      /^\d{4}年\d{1,2}月\d{1,2}日$/,
      // YYYY.MM.DD
      /^\d{4}\.\d{1,2}\.\d{1,2}$/
    ];
    
    // 处理中文日期格式
    if (dateString.includes('年')) {
      const match = dateString.match(/(\d{4})年(\d{1,2})月(\d{1,2})日/);
      if (match) {
        return new Date(match[1], parseInt(match[2]) - 1, match[3]);
      }
    }
    
    // 处理点分格式
    if (dateString.includes('.')) {
      const parts = dateString.split('.');
      if (parts.length === 3) {
        return new Date(parts[0], parseInt(parts[1]) - 1, parts[2]);
      }
    }
    
    // 处理斜杠格式
    if (dateString.includes('/')) {
      const parts = dateString.split('/');
      if (parts.length === 3) {
        // 判断是MM/DD/YYYY还是DD/MM/YYYY
        if (parseInt(parts[0]) > 12) {
          // DD/MM/YYYY
          return new Date(parts[2], parseInt(parts[1]) - 1, parts[0]);
        } else {
          // MM/DD/YYYY
          return new Date(parts[2], parseInt(parts[0]) - 1, parts[1]);
        }
      }
    }
    
    // 默认解析
    const parsedDate = new Date(dateString);
    return isNaN(parsedDate.getTime()) ? new Date() : parsedDate;
  }
  
  // 解析日期时间
  parseDateTime(dateTimeStr) {
    if (!dateTimeStr) return new Date();
    
    // 处理Excel日期序列号
    if (typeof dateTimeStr === 'number') {
      return new Date((dateTimeStr - 25569) * 86400 * 1000);
    }
    
    const parsedDateTime = new Date(dateTimeStr);
    return isNaN(parsedDateTime.getTime()) ? new Date() : parsedDateTime;
  }
  
  // 生成交易流水号
  generateTransactionId() {
    return `TXN${Date.now()}${Math.floor(Math.random() * 1000)}`;
  }
}

module.exports = ImportService;